/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nxlv2.nomad.accessdb.dao.jdbc;

import com.nxlv2.nomad.accessdb.dao.DAOException;
import com.nxlv2.nomad.accessdb.dao.IClientDao;
import com.nxlv2.nomad.accessdb.dao.IncoherentResultException;
import com.nxlv2.nomad.accessdb.dao.NoResultException;
import com.nxlv2.nomad.businessobjects.Adresse;
import com.nxlv2.nomad.businessobjects.Article;
import com.nxlv2.nomad.businessobjects.Client;
import com.nxlv2.nomad.businessobjects.Commande;
import com.nxlv2.nomad.businessobjects.Contact;
import com.nxlv2.nomad.businessobjects.Demande;
import com.nxlv2.nomad.businessobjects.EtatCommande;
import com.nxlv2.nomad.businessobjects.TypeDemande;
import com.nxlv2.nomad.businessobjects.Utilisateur;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Xavier
 */
public class JdbcClientDao implements IClientDao {

    private static final Logger logger = LoggerFactory.getLogger(JdbcClientDao.class);
    private static final ConnectionManager manager = new ConnectionManager();

    /**
     * 
     * @param client
     * @throws DAOException 
     */
    @Override
    public void saveClient(Client client) throws DAOException {
        if (null == client) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();

        builder.append("INSERT INTO client (idBase, nom, dateCreation) ");
        builder.append("VALUES (?, AES_ENCRYPT(?,?), NOW() )");

        String request = builder.toString();
        logger.debug("Insert client: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request, PreparedStatement.RETURN_GENERATED_KEYS);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, client.getIdBase());
            ps.setString(2, client.getNom());
            ps.setString(3, manager.getKeyAES());
            ps.executeUpdate();

        } catch (SQLException e) {
            logger.error("Can not populate request with data.",e);
            throw new DAOException("Can not populate request with data.", e);
        }

        try {
            ResultSet rs = ps.getGeneratedKeys();

            int pk = 0;

            while (rs.next()) {
                pk = rs.getInt(1);
            }
            client.setIdAppli(pk);

        } catch (SQLException e) {
            logger.error("Can not execute insert in db.");
            throw new DAOException("Can not execute insert in db.", e);
        }
        manager.fermerConnection();
    }

    /**
     * 
     * @param id
     * @throws DAOException 
     */
    @Override
    public void deleteClient(int id) throws DAOException {
        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("DELETE FROM client WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Delete client: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement", e);
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not delete client with id.", e);
            throw new DAOException("Can not delete client with id.", e);
        }
        manager.fermerConnection();
    }

    /**
     * 
     * @param client
     * @throws DAOException 
     */
    @Override
    public void updateClient(Client client) throws DAOException {
        if (null == client) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE client ");
        builder.append("SET idBase = ?, nom = AES_ENCRYPT(?,?),dateModification = NOW() ");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Update client: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, client.getIdBase());
            ps.setString(2, client.getNom());
            ps.setString(3, manager.getKeyAES());
            ps.setInt(4, client.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }
manager.fermerConnection();
    }

    /**
     * 
     * @param id
     * @return Client
     * @throws DAOException 
     */
    @Override
    public Client findClientById(int id) throws DAOException {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idAppli, idBase, ");
        builder.append("AES_DECRYPT(nom,?) AS nom ");
        builder.append("FROM client ");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("select client: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }
        try {
            ps.setString(1, manager.getKeyAES());
            ps.setInt(2, id);
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Client> clients = null;
        try {
            clients = map(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == clients || clients.isEmpty()) {
            throw new NoResultException("No client found with id " + id);
        }
        if (clients.size() > 1) {
            throw new IncoherentResultException("Many records with a same id.");
        }
        manager.fermerConnection();
        return clients.get(0);

    }
    
  @Override
    public Client findClientByIdbase(int id) throws DAOException {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idAppli, idBase, ");
        builder.append("AES_DECRYPT(nom,?) AS nom ");
        builder.append("FROM client ");
        builder.append("WHERE idBase = ?");

        String request = builder.toString();
        logger.debug("select client: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }
        try {
            ps.setString(1, manager.getKeyAES());
            ps.setInt(2, id);
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Client> clients = null;
        try {
            clients = map(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == clients || clients.isEmpty()) {
            throw new NoResultException("No client found with id " + id);
        }
        if (clients.size() > 1) {
            throw new IncoherentResultException("Many records with a same id.");
        }
        manager.fermerConnection();
        return clients.get(0);

    }
  
    @Override
    public boolean existNom(String nom, Utilisateur user) {       
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idAppli, idBase, ");
        builder.append("AES_DECRYPT(nom,?) AS nom ");
        builder.append("FROM client ");
        builder.append("WHERE LOWER(CONVERT(AES_DECRYPT(nom,?)  USING 'utf8') ) = LOWER(?) "
                + "AND idAppliUtilisateur = ?");

        String request = builder.toString();
        logger.debug("select client: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }
        try {
            ps.setString(1, manager.getKeyAES());
            ps.setString(2, manager.getKeyAES());
            ps.setString(3, nom);
            ps.setInt(4, user.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with nom.");
            throw new DAOException("Can not select data with nom.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Client> clients = null;
        try {
            clients = map(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }
        manager.fermerConnection();
        if (1 == clients.size()){
            return true;
        }
        if (null == clients || clients.isEmpty()) {
           return false;
        }
        if (clients.size() > 1) {
            throw new IncoherentResultException("Many records with a same nom.");
        }
        return false;
    }
    
    

    /**
     *
     * @param rst
     * @return List<Client>
     * @throws SQLException
     */
    private List<Client> map(ResultSet rst) throws SQLException {
        List<Client> clients = new ArrayList<>();

        while (rst.next()) {

            Client client = new Client();
            client.setIdAppli(rst.getInt("idAppli"));
            client.setIdBase(rst.getInt("idBase"));
            client.setNom(rst.getString("nom"));
            clients.add(client);
        }
        return clients;
    }

    /**
     *
     * @param rst
     * @return List<Client>
     * @throws SQLException
     */
    private List<Contact> mapContact(ResultSet rst) throws SQLException {
        List<Contact> contacts = new ArrayList<>();

        while (rst.next()) {
            Contact contact = new Contact();
            contact.setIdAppli(rst.getInt("idAppli"));
            contact.setIdBase(rst.getInt("idBase"));
            contact.setNom(rst.getString("nom"));
            contact.setPrenom(rst.getString("prenom"));
            contact.setMail(rst.getString("mail"));
            contact.setTel(rst.getString("tel"));
            contact.setDefaut(rst.getBoolean("defaut"));
            contacts.add(contact);
        }
        return contacts;
    }

    /**
     *
     * @param client
     * @return List<Contact>
     * @throws DAOException
     */
    @Override
    public List<Contact> getContactsParClient(Client client) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT contact.idBase, contact.idAppli,");
        builder.append("AES_DECRYPT(contact.nom,?) AS nom,");
        builder.append("AES_DECRYPT(contact.prenom,?) AS prenom,");
        builder.append("AES_DECRYPT(contact.mail,?) AS mail,");
        builder.append("AES_DECRYPT(contact.tel,?) AS tel, defaut ");
        builder.append("FROM contact ");
        builder.append("WHERE contact.idAppliClient = ?");

        String request = builder.toString();
        logger.debug("Select contacts par client: {}", request);
        logger.debug("client {}", client);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, manager.getKeyAES());
            ps.setString(2, manager.getKeyAES());
            ps.setString(3, manager.getKeyAES());
            ps.setString(4, manager.getKeyAES());
            ps.setInt(5, client.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Contact> contacts = null;
        try {
            contacts = mapContact(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == contacts || contacts.isEmpty()) {
//            throw new NoResultException("No contact found with id " + client.getIdAppli());
        }
manager.fermerConnection();
        return contacts;

    }
    /**
     *
     * @param client
     * @return List<Contact>
     * @throws DAOException
     */
    @Override
    public List<Contact> getContactsMajParClient(Client client) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT contact.idBase, contact.idAppli,");
        builder.append("AES_DECRYPT(contact.nom,?) AS nom,");
        builder.append("AES_DECRYPT(contact.prenom,?) AS prenom,");
        builder.append("AES_DECRYPT(contact.mail,?) AS mail,");
        builder.append("AES_DECRYPT(contact.tel,?) AS tel, defaut ");
        builder.append("FROM contact "
                + "JOIN client ON client.idAppli = contact.idAppliClient "
                + "JOIN utilisateur ON utilisateur.idAppli = client.idAppliUtilisateur ");
        builder.append("WHERE contact.idAppliClient = ? "
                + "AND (contact.idBase = 0 OR contact.dateModification > utilisateur.dateSynchro)");

        String request = builder.toString();
        logger.debug("Select contacts par client: {}", request);
        logger.debug("client {}", client);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, manager.getKeyAES());
            ps.setString(2, manager.getKeyAES());
            ps.setString(3, manager.getKeyAES());
            ps.setString(4, manager.getKeyAES());
            ps.setInt(5, client.getIdAppli());          
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Contact> contacts = null;
        try {
            contacts = mapContact(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == contacts || contacts.isEmpty()) {
//            throw new NoResultException("No contact found with id " + client.getIdAppli());
        }
manager.fermerConnection();
        return contacts;

    }

    /**
     * 
     * @param contact
     * @param client
     * @throws DAOException 
     */
    @Override
    public void lierContactAClient(Contact contact, Client client)throws DAOException {
        if (null == client || null == contact) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();

        builder.append("UPDATE contact ");
        builder.append("SET idAppliClient = ?,");
        builder.append("dateModification = NOW()");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Lier contact à client: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, client.getIdAppli());
            ps.setInt(2, contact.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not link contact to client.");
            throw new DAOException("Can not link contact to client.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param client
     * @return listAdresse
     * @throws DAOException
     */
    @Override
    public List<Adresse> getAdressesParClient(Client client) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT adresse.idBase, adresse.idAppli,");
        builder.append("AES_DECRYPT(adresse.numVoie,?) AS numVoie,");
        builder.append("AES_DECRYPT(adresse.nomVoie,?) AS nomVoie,");
        builder.append("AES_DECRYPT(adresse.complement,?) AS complement,");
        builder.append("AES_DECRYPT(adresse.codePostal,?) AS codePostal,");
        builder.append("AES_DECRYPT(adresse.ville,?) AS ville,");
        builder.append("AES_DECRYPT(adresse.pays,?) AS pays,");
        builder.append("AES_DECRYPT(adresse.mail,?) AS mail,");
        builder.append("AES_DECRYPT(adresse.tel,?) AS tel, adresse.defaut ");
        builder.append("FROM adresse ");
        builder.append("WHERE adresse.idAppliClient = ?");

        String request = builder.toString();
        logger.debug("Select adresses par client: {}", request);
        logger.debug("client {}", client);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, manager.getKeyAES());
            ps.setString(2, manager.getKeyAES());
            ps.setString(3, manager.getKeyAES());
            ps.setString(4, manager.getKeyAES());
            ps.setString(5, manager.getKeyAES());
            ps.setString(6, manager.getKeyAES());
            ps.setString(7, manager.getKeyAES());
            ps.setString(8, manager.getKeyAES());
            ps.setInt(9, client.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Adresse> adresses = null;
        try {
            adresses = mapAdresse(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }
manager.fermerConnection();
        return adresses;
    }
    /**
     *
     * @param client
     * @return listAdresse
     * @throws DAOException
     */
    @Override
    public List<Adresse> getAdressesMajParClient(Client client) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT adresse.idBase, adresse.idAppli,");
        builder.append("AES_DECRYPT(adresse.numVoie,?) AS numVoie,");
        builder.append("AES_DECRYPT(adresse.nomVoie,?) AS nomVoie,");
        builder.append("AES_DECRYPT(adresse.complement,?) AS complement,");
        builder.append("AES_DECRYPT(adresse.codePostal,?) AS codePostal,");
        builder.append("AES_DECRYPT(adresse.ville,?) AS ville,");
        builder.append("AES_DECRYPT(adresse.pays,?) AS pays,");
        builder.append("AES_DECRYPT(adresse.mail,?) AS mail,");
        builder.append("AES_DECRYPT(adresse.tel,?) AS tel, adresse.defaut ");
        builder.append("FROM adresse "
                + "JOIN client ON client.idAppli = adresse.idAppliClient "
                + "JOIN utilisateur ON utilisateur.idAppli = client.idAppliUtilisateur ");
        builder.append("WHERE adresse.idAppliClient = ? "
                + "AND (adresse.idBase = 0 OR adresse.dateModification > utilisateur.dateSynchro)");

        String request = builder.toString();
        logger.debug("Select adresses par client: {}", request);
        logger.debug("client {}", client);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, manager.getKeyAES());
            ps.setString(2, manager.getKeyAES());
            ps.setString(3, manager.getKeyAES());
            ps.setString(4, manager.getKeyAES());
            ps.setString(5, manager.getKeyAES());
            ps.setString(6, manager.getKeyAES());
            ps.setString(7, manager.getKeyAES());
            ps.setString(8, manager.getKeyAES());
            ps.setInt(9, client.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Adresse> adresses = null;
        try {
            adresses = mapAdresse(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }
manager.fermerConnection();
        return adresses;
    }

    /**
     *
     * @param rst
     * @return List<Adresse>
     * @throws SQLException
     */
    private List<Adresse> mapAdresse(ResultSet rst) throws SQLException {
        List<Adresse> adresses = new ArrayList<>();

        while (rst.next()) {
            Adresse adresse = new Adresse();
            adresse.setIdAppli(rst.getInt("idAppli"));
            adresse.setIdBase(rst.getInt("idBase"));
            adresse.setNumVoie(rst.getString("numVoie"));
            adresse.setNomVoie(rst.getString("nomVoie"));
            adresse.setComplement(rst.getString("complement"));
            adresse.setCodePostal(rst.getString("codePostal"));
            adresse.setVille(rst.getString("ville"));
            adresse.setPays(rst.getString("pays"));
            adresse.setMail(rst.getString("mail"));
            adresse.setTel(rst.getString("tel"));
            adresse.setDefaut(rst.getBoolean("defaut"));

            adresses.add(adresse);
        }
        return adresses;
    }

    /**
     * 
     * @param adresse
     * @param client
     * @throws DAOException 
     */
    @Override
    public void lierAdresseAClient(Adresse adresse, Client client)throws DAOException {

        if (null == client || null == adresse) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();

        builder.append("UPDATE adresse ");
        builder.append("SET idAppliClient = ?,");
        builder.append("dateModification = NOW()");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Lier adresse à client: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, client.getIdAppli());
            ps.setInt(2, adresse.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not link adresse to client.");
            throw new DAOException("Can not link adresse to client.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param client
     * @return listArticle
     * @throws DAOException
     */
    @Override
    public List<Article> getArticlesParClient(Client client) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT article.idBase, article.idAppli,");
        builder.append("AES_DECRYPT(article.libelle,?) AS libelle,");
        builder.append("AES_DECRYPT(article.reference,?) AS reference ");
        builder.append("FROM article ");
        builder.append("WHERE article.idAppliClient = ?");

        String request = builder.toString();
        logger.debug("Select articles par client: {}", request);
        logger.debug("client {}", client);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, manager.getKeyAES());
            ps.setString(2, manager.getKeyAES());
            ps.setInt(3, client.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Article> articles = null;
        try {
            articles = mapArticle(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

//        if (null == articles || articles.isEmpty()) {
//            throw new NoResultException("No article found with id " + client.getIdAppli());
//        }
manager.fermerConnection();
        return articles;
    }
    /**
     *
     * @param client
     * @return listArticle
     * @throws DAOException
     */
    @Override
    public List<Article> getArticlesMajParClient(Client client) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT article.idBase, article.idAppli,");
        builder.append("AES_DECRYPT(article.libelle,?) AS libelle,");
        builder.append("AES_DECRYPT(article.reference,?) AS reference ");
        builder.append("FROM article "
                + "JOIN client ON client.idAppli = article.idAppliClient "
                + "JOIN utilisateur ON utilisateur.idAppli = client.idAppliUtilisateur ");
        builder.append("WHERE article.idAppliClient = ? "
                + "AND (article.idBase = 0 OR article.dateModification > utilisateur.dateSynchro)");

        String request = builder.toString();
        logger.debug("Select articles par client: {}", request);
        logger.debug("client {}", client);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, manager.getKeyAES());
            ps.setString(2, manager.getKeyAES());
            ps.setInt(3, client.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Article> articles = null;
        try {
            articles = mapArticle(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

//        if (null == articles || articles.isEmpty()) {
//            throw new NoResultException("No article found with id " + client.getIdAppli());
//        }
manager.fermerConnection();
        return articles;
    }

    /**
     *
     * @param rst
     * @return List<Article>
     * @throws SQLException
     */
    private List<Article> mapArticle(ResultSet rst) throws SQLException {
        List<Article> articles = new ArrayList<>();

        while (rst.next()) {
            Article article = new Article();
            article.setIdBase(rst.getInt("idBase"));
            article.setIdAppli(rst.getInt("idAppli"));
            article.setLibelle(rst.getString("libelle"));
            article.setReference(rst.getString("reference"));
            articles.add(article);
        }
        return articles;
    }

    /**
     * 
     * @param article
     * @param client
     * @throws DAOException 
     */
    @Override
    public void lierArticleAClient(Article article, Client client)throws DAOException {

        if (null == client || null == article) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();

        builder.append("UPDATE article ");
        builder.append("SET idAppliClient = ?,");
        builder.append("dateModification = NOW()");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Lier article à client: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, client.getIdAppli());
            ps.setInt(2, article.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not link article to client.");
            throw new DAOException("Can not link article to client.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param client
     * @return listCommande
     * @throws DAOException
     */
    @Override
    public List<Commande> getCommandesParClient(Client client) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT commande.idBase, commande.idAppli, commande.date, commande.numCommande, ");
        builder.append("AES_DECRYPT(commande.etatCommande,?) AS etatCommande, commande.idAdresseFacturation, commande.idAdresseLivraison ");
        builder.append("FROM commande ");
        builder.append("WHERE commande.idAppliClient = ?");

        String request = builder.toString();
        logger.debug("Select commandes par client: {}", request);
        logger.debug("client {}", client);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, manager.getKeyAES());
            ps.setInt(2, client.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Commande> commandes = null;
        try {
            commandes = mapCommande(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

//        if (null == commandes || commandes.isEmpty()) {
//            throw new NoResultException("No commande found with id " + client.getIdAppli());
//        }

        return commandes;

    }
    /**
     *
     * @param client
     * @return listCommande
     * @throws DAOException
     */
    @Override
    public List<Commande> getCommandesMajParClient(Client client) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT commande.idBase, commande.idAppli, commande.date, commande.numCommande, ");
        builder.append("AES_DECRYPT(commande.etatCommande,?) AS etatCommande, commande.idAdresseFacturation, commande.idAdresseLivraison ");
        builder.append("FROM commande "
                + "JOIN client c ON c.idAppli = commande.idAppliClient "
                + "JOIN utilisateur u ON u.idAppli = c.idAppliUtilisateur ");
        builder.append("WHERE commande.idAppliClient = ? "
                + "AND (commande.idBase = 0 OR commande.dateModification > u.dateSynchro)");

        String request = builder.toString();
        logger.debug("Select commandes par client: {}", request);
        logger.debug("client {}", client);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, manager.getKeyAES());
            ps.setInt(2, client.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Commande> commandes = null;
        try {
            commandes = mapCommande(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

//        if (null == commandes || commandes.isEmpty()) {
//            throw new NoResultException("No commande found with id " + client.getIdAppli());
//        }

        return commandes;

    }

    private List<Commande> mapCommande(ResultSet rst) throws SQLException {
        List<Commande> commandes = new ArrayList<>();

        while (rst.next()) {
            Commande commande = new Commande();
            commande.setIdBase(rst.getInt("idBase"));
            commande.setIdAppli(rst.getInt("idAppli"));
            commande.setNumCommande(rst.getLong("numCommande"));
            commande.setEtatCommande(EtatCommande.getByLibelle(rst.getString("etatCommande")));
            commande.setIdAdresseFacturation(rst.getInt("idAdresseFacturation"));
            commande.setIdAdresseLivraison(rst.getInt("idAdresseLivraison"));
            commande.setDate(rst.getDate("date"));
            commandes.add(commande);
        }
        return commandes;
    }

    /**
     * 
     * @param commande
     * @param client
     * @throws DAOException 
     */
    @Override
    public void lierCommandeAClient(Commande commande, Client client)throws DAOException {

        if (null == client || null == commande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();

        builder.append("UPDATE commande ");
        builder.append("SET idAppliClient = ?,");
        builder.append("dateModification = NOW()");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Lier commande à client: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, client.getIdAppli());
            ps.setInt(2, commande.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not link commande to client.");
            throw new DAOException("Can not link commande to client.", e);
        }
        manager.fermerConnection();
    }

    /**
     * 
     * @param client
     * @return
     * @throws DAOException 
     */
    @Override
    public List<Demande> getDemandesParClient(Client client) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT demande.idBase, demande.idAppli, demande.numDemande, demande.valideDemande, demande.date, "
                + "demande.idAdresseFacturation, demande.idAdresseLivraison, demande.idAppliTypeDemande ");
        builder.append("FROM demande ");
        builder.append("WHERE demande.idAppliClient = ?");

        String request = builder.toString();
        logger.debug("Select demandes par client: {}", request);
        logger.debug("client {}", client);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, client.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Demande> demandes = null;
        try {
            demandes = mapDemande(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

//        if (null == demandes || demandes.isEmpty()) {
//            throw new NoResultException("No demande found with id " + client.getIdAppli());
//        }
manager.fermerConnection();
        return demandes;

    }
    /**
     * 
     * @param client
     * @return
     * @throws DAOException 
     */
    @Override
    public List<Demande> getDemandesMajParClient(Client client) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT d.idBase, d.idAppli, d.numDemande, d.valideDemande, d.date, "
                + "d.idAdresseFacturation, d.idAdresseLivraison, d.idAppliTypeDemande ");
        builder.append("FROM demande d "
                + "JOIN client c ON c.idAppli = d.idAppliClient "
                + "JOIN utilisateur u ON u.idAppli = c.idAppliUtilisateur ");
        builder.append("WHERE d.idAppliClient = ? "
                + "AND (d.idBase = 0 OR d.dateModification > u.dateSynchro)");

        String request = builder.toString();
        logger.debug("Select demandes par client: {}", request);
        logger.debug("client {}", client);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, client.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Demande> demandes = null;
        try {
            demandes = mapDemande(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

//        if (null == demandes || demandes.isEmpty()) {
//            throw new NoResultException("No demande found with id " + client.getIdAppli());
//        }
manager.fermerConnection();
        return demandes;

    }

    private List<Demande> mapDemande(ResultSet rst) throws SQLException {
        List<Demande> demandes = new ArrayList<>();

        while (rst.next()) {
            Demande demande = new Demande();
            demande.setIdBase(rst.getInt("idBase"));
            demande.setIdAppli(rst.getInt("idAppli"));
            demande.setNumDemande(rst.getLong("numDemande"));
            demande.setValideDemande(rst.getInt("valideDemande"));
            demande.setDate(rst.getDate("date"));
            demande.setIdAdresseFacturation(rst.getInt("idAdresseFacturation"));
            demande.setIdAdresseLivraison(rst.getInt("idAdresseLivraison"));
            demande.setTypeDemande(TypeDemande.getByID(rst.getInt("idAppliTypeDemande")));
            demandes.add(demande);
        }
        return demandes;
    }

    /**
     * 
     * @param demande
     * @param client
     * @throws DAOException 
     */
    @Override
    public void lierDemandeAClient(Demande demande, Client client) throws DAOException{

        if (null == client || null == demande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();

        builder.append("UPDATE demande ");
        builder.append("SET idAppliClient = ?, ");
        builder.append("dateModification = NOW() ");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Lier demande à client: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, client.getIdAppli());
            ps.setInt(2, demande.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not link demande to client.");
            throw new DAOException("Can not link demande to client.", e);
        }
        manager.fermerConnection();
    }
}
